#!/usr/local/python36/bin/python3.6
#-*- coding:utf-8 -*-

from china_map.models import u_city_s,u_province_s,c_city_s,c_province_s
from datetime import datetime, timedelta
from django.db import connection, transaction
from django.db.utils import OperationalError
import django.utils.timezone as timezone
import pymysql


def mysql_con():
    host = ''
    dbname = ''
    user = ''
    pwd = ''
    db = pymysql.connect(host, user, pwd, dbname, charset='utf8')
    cursor = db.cursor()
    return cursor


def add_province(p_data):
    date1 = datetime.now()
    date2 = datetime.strftime(date1, "%Y%m%d")
    cursor = connection.cursor()
    for name,num in p_data.items():
        cursor.execute("SELECT count FROM u_province_s WHERE provincename ='" + name + "' AND DATE_FORMAT(createTime,'%Y%m%d')=" + date2)
        row = cursor.fetchone()
        if row == None:
            cursor.execute("SELECT provinceId FROM c_province_s  WHERE provincename='" + name +"'")
            pid2 = cursor.fetchone()
            pid3 = str(pid2[0])
            cursor.execute('INSERT INTO u_province_s (provinceId,provincename,count,createTime) VALUE (%s,%s,%s,%s)',[pid3, name, num, timezone.now()])
        else:
            
            num1 = int(row[0])
            num1 += num
            cursor.execute("UPDATE u_province_s SET count = " + str(num1) + " where DATE_FORMAT(createTime,'%Y%m%d')=" + date2 + " AND provincename= '" + name + "'")
    cursor.close()




def add_city(c_data):
    date1 = datetime.now()
    date2 = datetime.strftime(date1, "%Y%m%d")
    cursor = connection.cursor()
    for name,num in c_data.items():
        cursor.execute("SELECT count FROM u_city_s WHERE cityname ='" + name + "' AND DATE_FORMAT(createTime,'%Y%m%d')=" + date2)
        row = cursor.fetchone()
        if row == None:
            print(name,'================')
            cursor.execute("SELECT cityId,provinceId FROM c_city_s  WHERE cityname='" + name +"'")
            pid2 = cursor.fetchone()
            pid3 = str(pid2[0])
            pid4 = str(pid2[1])
            cursor.execute('INSERT INTO u_city_s (cityId,provinceId,cityname,count,createTime) VALUE (%s,%s,%s,%s,%s)',[pid3,pid4, name, num, timezone.now()])
        else:
            num1 = int(row[0])
            num1 += num
            cursor.execute("UPDATE u_city_s SET count = " + str(num1) + " where DATE_FORMAT(createTime,'%Y%m%d')=" + date2 + " AND cityname= '" + name + "'")
    cursor.close()




def sel_province(city_list):
    date1 = datetime.now()
    date2 = datetime.strftime(date1, "%Y%m%d")
    city_dist = dict.fromkeys(city_list, 0)
    cursor = connection.cursor()
    cursor.execute("SELECT provincename,count FROM u_province_s WHERE DATE_FORMAT(createTime,'%Y%m%d') =" + date2)
    for row in cursor.fetchall():
        city_dist[row[0]] = int(row[1])
    cursor.close()
    return city_dist

def sel_city(cityname):
    se_cursor = mysql_con()
    se_cursor.execute("SELECT * FROM (SELECT ( SELECT ( SELECT z2.regionName FROM zy_region z2 WHERE z1.parentId = z2.regionId ) AS regionName FROM zy_region z1 WHERE z1.regionId = e.regionId ) AS regionname, e.companyName, COUNT(1) AS on_shop_sum FROM s_shop_device c LEFT JOIN s_shop d ON c.shopId = d.shopId LEFT JOIN y_info_company e ON e.companyId = d.companyId WHERE c.isDelete = 0 AND e.companyId NOT IN (0, 1, 2, 3, 4, 8) AND c.onLineState = 0 AND c.deviceStyle IN (3, 4) GROUP BY d.companyId ) t ORDER BY t.on_shop_sum DESC")
    se_data = se_cursor.fetchall()

    cityl = []
    cursor = connection.cursor()
    cursor.execute("SELECT provinceId FROM c_province_s WHERE provincename = %s",[cityname])
    pid1 = cursor.fetchone()
    pid2 = str(pid1[0])
    cursor.execute("SELECT cityname FROM c_city_s WHERE provinceId = %s", [pid2])
    for row in cursor.fetchall():
        cityl.append(row[0])
    cityd = dict.fromkeys(cityl, 0)

    if se_data[0][0] == ():
        pass
    else:
        for row in se_data:
            if row[0] in cityl:
                cityd[row[0]] = int(row[2])
    cursor.close()
    se_cursor.close()
    return cityd

def top_province():
    date1 = datetime.now()
    date2 = datetime.strftime(date1, "%Y%m%d")
    cursor = connection.cursor()
    topData = {}
    topbeast = {}
    timeLineData = []
    years = str(datetime.now().year)
    mouths = ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12']
    for mouth in mouths:
        newdate = years + '0' + mouth
        cursor.execute("SELECT provincename,SUM(count) as counts FROM u_province_s WHERE DATE_FORMAT(createTime,'%Y%m')=" + newdate + " GROUP BY provincename ORDER BY counts desc")
        rows = cursor.fetchall()
        l1 = []
        l2 = []
        if not rows:
            pass
        else:
            for row in rows:
                l1.append(row[0])
                l2.append(int(row[1]))
                if len(l1) == 10:
                    break
        if l1:
            topData[mouth] = l1
            topbeast[mouth] = l2
    cursor.close()
    return topData, topbeast

def after_province():
    ap_cursor = mysql_con()
    ap_sql_per = ap_cursor.execute("SELECT * FROM (SELECT ( SELECT ( SELECT ( SELECT z3.regionName FROM zy_region z3 WHERE z2.parentId = z3.regionId ) FROM zy_region z2 WHERE z1.parentId = z2.regionId ) AS regionName FROM zy_region z1 WHERE z1.regionId = y.regionId ) AS regionname, y.companyName, COUNT(1) AS shop_sum FROM s_shop s LEFT JOIN y_info_company y ON s.companyId = y.companyId WHERE y.isUsing = 1 AND s.isUsing = 1 AND s.shopState = 1 AND s.isDelete = 0 AND s.companyId NOT IN (0, 1, 2, 3, 4, 8) GROUP BY s.companyId ) t ORDER BY t.shop_sum DESC LIMIT 6")
    ap_sql_data = ap_cursor.fetchall()
    ap_shop_name = []
    ap_shop_data = []
    if not ap_sql_data:
        pass
    else:
        for ap_one in ap_sql_data:
            ap_shop_name.append(ap_one[1])
            ap_shop_data.append(ap_one[2])
    return ap_shop_name, ap_shop_data

def insert_all(request_data_list):
    date1 = datetime.now()
    date2 = datetime.strftime(date1, "%Y%m%d")
    cursor = connection.cursor()
    print(request_data_list)
    for request_data in request_data_list:
        a = request_data["requestAt"]
        GMT_FORMAT = '%b %d, %Y %H:%M:%S %p'
        b = datetime.strptime(a,GMT_FORMAT)
        cursor.execute('INSERT INTO all_request (origin, requestAt, url, parameters, consume, sessionId, createTime) VALUE (%s,%s,%s,%s,%s,%s,%s)', [request_data["origin"], b, request_data["url"], str(request_data["parameters"]), request_data["consume"], request_data["sessionId"], timezone.now()])
    cursor.close()
